<?php


class luck
{
    public function getUser($name)
    {
        $pdo = new Mypdo();
        $sql = "select * from user where name = '{$name}'";
        return $pdo->PdoQueryOne($sql);
    }

    public function getUserCount()
    {
        $pdo = new Mypdo();
        $sql = "select count(id) as countId from user";
        return $pdo->PdoQueryOne($sql);
    }

    public function getUserById($userId, $select)
    {
        $pdo = new Mypdo();
        $sql = "select 
                {$select}
                from user where id = '{$userId}'";
        return $pdo->PdoQueryOne($sql);
    }

    public function UpDataUserQueue($Data)
    {
        $pdo = new Mypdo();
        return $pdo->edit($Data, 'id = 1', 'user_queue');
    }

    public function getUserQueue()
    {
        $pdo = new Mypdo();
        $sql = "select * from user_queue where id = 1";
        return $pdo->PdoQueryOne($sql);
    }

    public function getAssessorAccount($mobile)
    {
        $pdo = new Mypdo();
        $sql = "select * from assessor where mobile = '{$mobile}'";
        return $pdo->PdoQueryOne($sql);
    }

    public function getAssessorById($id)
    {
        $pdo = new Mypdo();
        $sql = "select * from assessor where id = '{$id}'";
        return $pdo->PdoQueryOne($sql);
    }

    /**
     * 获取所有评委
     * getAssessor
     * @return mixed
     * USER zengqilong
     * 2024/1/16 16:13
     */
    public function getAssessor()
    {
        $pdo = new Mypdo();
        $sql = "select * from assessor order by second asc";
        return $pdo->PdoQueryAll($sql);
    }

    public function insertScore($data)
    {
        $pdo = new Mypdo();
        return $pdo->Insert($data, 'user_score');
    }

    public function getUserScoreByUserIdAndAssessorId($userId, $assessorId)
    {
        $pdo = new Mypdo();
        $sql = "select * from user_score where user_id = '{$userId}' and Assessor_id = '{$assessorId}'";
        return $pdo->PdoQueryOne($sql);
    }
    public function getUserList()
    {
        $pdo = new Mypdo();
        $sql = "select * from user";
        return $pdo->PdoQueryAll($sql);
    }
    public function getUserScoreByAssessorId($assessorId)
    {
        $pdo = new Mypdo();
        $sql = "select * from user_score where Assessor_id = '{$assessorId}'";
        return $pdo->PdoQueryAll($sql);
    }

    public function getUserScoreById($id)
    {
        $pdo = new Mypdo();
        $sql = "select * from user_score where id = '{$id}'";
        return $pdo->PdoQueryOne($sql);
    }

    public function getUserScore()
    {
        $pdo = new Mypdo();
        $sql = "select user_name, sum(score) as score from user_score group by user_id order by sum(score) desc";

        return $pdo->PdoQueryAll($sql);
    }

    public function getUserScoreGroup()
    {
        $pdo = new Mypdo();
        $sql = "SELECT `group`, COUNT(DISTINCT user_score.user_id) AS countId, sum(score)/count(DISTINCT user_id) AS score FROM user_score LEFT JOIN `user` ON `user`.id = user_score.user_id GROUP BY	`user`.`group` ORDER BY	score DESC";

        return $pdo->PdoQueryAll($sql);
    }

    public function getUserScoreProfile()
    {
        $pdo = new Mypdo();
        $sql = "SELECT user_id,user_name,SUM(first+second+third) as sumScore,score FROM user_score GROUP BY user_id order BY sumScore DESC,score DESC";

        return $pdo->PdoQueryAll($sql);
    }

    public function getUserScoreByUserName($name)
    {
        $pdo = new Mypdo();
        $sql = "select Assessor_id,Assessor_name from user_score where user_name = '{$name}'";
        return $pdo->PdoQueryAll($sql);
    }

    public function UpdateScoreById($id, $update)
    {
        $pdo = new Mypdo();
        return $pdo->edit($update, "id = {$id}", 'user_score');
    }

    public function getIsOkUser()
    {
        $pdo = new Mypdo();
        $sql = "select count(id) as countId from user_score group by Assessor_id";
        return $pdo->PdoQueryOne($sql);
    }

    public function getUserNotInScore($aId)
    {
        $pdo = new Mypdo();
        $sql = "SELECT user_name AS name, user_id AS id FROM user_score u1 WHERE Assessor_id != {$aId} AND NOT EXISTS ( SELECT 1 FROM user_score u2 WHERE u2.user_id = u1.user_id AND u2.Assessor_id = {$aId} ) GROUP BY user_id";
        return $pdo->PdoQueryAll($sql);
    }
}